# -*- coding: utf-8 -*-
# @Time :2020/5/20 0020 20:53
# @Author :bamboo
# @Email : xb@gistide.com
# @File :readExcel.py
# @Software: PyCharm
#-*- encoding:utf-8 -*-
import sys
import locale
import os.path
import os
import time
import shutil
from datetime import datetime
import types
import sqlite3
# import pypyodbc
import traceback
import json
import codecs
import xlrd
import xlwt
from xlrd import xldate_as_tuple
from xlutils.copy import copy
import numpy as np
from pypinyin import lazy_pinyin, Style


class Excelcheck:
    def __init__(self,file,schema={}):
        self.rows_start=schema.setdefault("rows_start",0)
        self.cols_start=schema.setdefault("cols_start",0)
        self.sheets_index=schema.setdefault("sheets_index",0)
        self.header=schema.setdefault("header",[])
        self.file=file


    def getdffheader(self,header,schema_header=None):
        '''
        @desc 检查Excel标题是否符合规范
        @date 2020/5/20
        '''
        if schema_header is None:
           schema_header=self.schema_header
        if len(schema_header) <= 0:
            return header
        if len(header) <= 0:
            return schema_header
        if isinstance(header,list):
            diff=np.setdiff1d(header,schema_header)
        if diff.size!=0:
            return diff.tolist()
        else:
            return  None
    def geterrorvalues(self,values,schema_values):
        '''
        @desc 检查Excel表格各列数据是否合规
        @date 2020/5/20
        '''
    def _getheader(self,sheet):
        return [sheet.cell(0, i).value.replace("\n", "") for i in range(sheet.ncols)]
    def getheader(self,file=None):
        file=self.file if not file else file
        try:
            if file.endswith('.xls'):
                data = xlrd.open_workbook(file, formatting_info=True)
            else:
                data = xlrd.open_workbook(r'{}'.format(file))
        except:
            print("文件{}错误".format(file))
            print('traceback.print_exc():', traceback.print_exc())
            print('traceback.format_exc():', traceback.format_exc())

        sheet = data.sheets()[self.sheets_index]
        # self._get_header(sheet)
        return self._get_header(sheet)
    def getvalue(self):
        pass
    def _readexcel(self):
        pass
    def _list2pinyin(self,values):
        if isinstance(values,list):
           return [lazy_pinyin(value) for value in values]
        if isinstance(values,str):
           return lazy_pinyin(values)

    def header2pin(self,header=None):
        arr1=[]
        arr2=[]
        arr3 = {}
        for d in header:
            if len(d) > 3:
                t = lazy_pinyin(d, style=Style.FIRST_LETTER)
            else:
                t = lazy_pinyin(d)
            arr1.append({'fieldname': "".join(t).lower(), 'fieldalias': d})
            arr2.append("".join(t).lower())
            arr3["".join(t).lower()]=d
        return dict({'filed':arr1,'filed1':arr2,'filed2':arr3})
    def to_json(self,file=None,isJson=None):
        #xlrd.Book.encoding = "gbk"
        file = self.file if not file else file
        try:
            if file.endswith('.xls'):
                data = xlrd.open_workbook(file, formatting_info=True)
            else:
                data = xlrd.open_workbook(r'{}'.format(file))
        except:
            print("文件{}错误".format(file))
            print('traceback.print_exc():', traceback.print_exc())
            print('traceback.format_exc():', traceback.format_exc())
        res=[]
        for t in data.sheets():

            # table = data.sheets()[self.sheets_index]
            table = t
            #找到有几列几列
            nrows = table.nrows #行数
            ncols = table.ncols #列数
            header = self.getheader(file)
            header = [r if len(r)>0 else 0 for r in header]

            # table = data.sheet_by_index(0)
            dataFile = []
            for iRow in range(1, nrows):
                row_values = table.row_values(iRow)
                if row_values:
                    str_obj = {}
                for iCol in range(ncols):
                    sCell = table.cell_value(iRow, iCol)

                    # Python读Excel，返回的单元格内容的类型有5种：
                    # ctype： 0 empty,1 string, 2 number, 3 date, 4 boolean, 5 error
                    ctype = table.cell(iRow, iCol).ctype
                    # ctype =3,为日期
                    if ctype == 3:
                        try:
                            sCell = xlrd.xldate.xldate_as_datetime(sCell, 0)
                            # date = datetime(*xldate_as_tuple(sCell, 0))
                            # sCell = date.strftime('%Y-%m-%d  %H:%M:%S')  # ('%Y/%m/%d %H:%M:%S')
                        except:
                            print("sCell, iRow,iCol===={}错误".format(sCell, iRow, iCol))
                            print('traceback.print_exc():', traceback.print_exc())
                            print('traceback.format_exc():', traceback.format_exc())
                            sCell = '1970-01-01 00:00:00'
                        # print(sCell)
                        table.row_values(iRow)[iCol] = sCell
                    # ctype =1，number
                    elif ctype == 2:
                        if sCell == '':
                            sCell = 0
                        table.row_values(iRow)[iCol] = sCell
                    # ctype =1，为字符串
                    elif ctype == 1:
                        if self.isVaildDate(sCell):
                            t1 = time.strptime(sCell, "%Y-%m-%d")
                            sCell = self.changeStrToDate(t1, "yyyy-mm-dd")
                            # print(sCell)
                            table.row_values(iRow)[iCol] = sCell
                    elif ctype == 4:
                        sCell = True if sCell == 1 else False
                    try:
                        str_obj[header[iCol]] = sCell
                    except:
                        print("sCell, iRow,iCol===={} == {}== {} 错误".format(sCell, iRow, iCol))
                        print("header===={} 错误".format(header))
                        print('traceback.print_exc():', traceback.print_exc())
                        print('traceback.format_exc():', traceback.format_exc())
                        sCell = '1970-01-01 00:00:00'
                if table.row_values(iRow)[0] != '':
                    dataFile.append(str_obj)
            # for rowNum in range(table.nrows):
            #     if table.row_values(rowNum)[0] != '':
            #         dataFile.append(table.row_values(rowNum))
            #删除首列为空的数据
            # i=0
            # while i < len(totalarray):
            #     if totalarray[i][0] == '':
            #         totalarray.pop(i)
            #         i -= 1
            #     i += 1
            res1 = {}
            res1['name'] = t.name
            res1['header'] = header
            #去除首行数据
            # dataFile.pop(0)
            res1['main'] = dataFile
            self.sheets_index=self.sheets_index+1
            res.append(res1)
        if isJson is None:
            return res
        else:
            return json.dumps(res,ensure_ascii=False)

    def getdate(date):
        __s_date = datetime.date(1899, 12, 31).toordinal()-1
        if isinstance(date, float):
            date = int(date)
        d = datetime.date.fromordinal(__s_date + date)
        return d.strftime("%Y-%m-%d %H:%M:%S")
    def formatDay(self,sDay, sFormat):
        sYear = str(sDay.year)
        sMonth = str(sDay.month)
        sDay = str(sDay.day)

        if sFormat == "yyyy-mm-dd":
            sFormatDay = sYear + "-" + sMonth.zfill(2) + "-" + sDay.zfill(2)
        elif sFormat == "yyyy/mm/dd":
            sFormatDay = sYear + "/" + sMonth.zfill(2) + "/" + sDay.zfill(2)
        else:
            sFormatDay = sYear + "-" + sMonth + "-" + sDay

        return sFormatDay

    """
    功能：判断是否为日期
    """

    def isVaildDate(self,sDate):
        try:
            if ":" in sDate:
                time.strptime(sDate, "%Y-%m-%d %H:%M:%S")
            else:
                time.strptime(sDate, "%Y-%m-%d")
            return True
        except:
            return False

    """
       功能：把字符串格式的日期转换为格式化的日期，如把2019-7-1转换为2019-07-01
    """

    def changeStrToDate(self,sDate, sFormat):
        sYear = str(sDate.tm_year)
        sMonth = str(sDate.tm_mon)
        sDay = str(sDate.tm_mday)

        if sFormat == "yyyy-mm-dd":
            sFormatDay = sYear + "-" + sMonth.zfill(2) + "-" + sDay.zfill(2)
        elif sFormat == "yyyy/mm/dd":
            sFormatDay = sYear + "/" + sMonth.zfill(2) + "/" + sDay.zfill(2)
        else:
            sFormatDay = sYear + "-" + sMonth + "-" + sDay

        return sFormatDay
    def _get_header(self,table):
        cells=table.merged_cells
        # L=np.array(cells)
        # print(L.ndim)
        # print(cells)
        dic=dict()
        # print(cells)
        row=0
        #判断为空的表格
        # for i in range(table.ncols):
        #     if table.cell_value(row, i) !="":
        #        arr.append({"id:":(row,i),"value":table.cell_value(row, i),'dict':None})
        #             # print(table.cell_value(i,j))
        # print(arr)
        if len(cells) > 0:
            for cell in cells:
                a, b, c, d = cell
                # print("第{}行，第{}列，融合{}行，融合{}列，值:{}".format(a+1,c+1,b-a,d-c,table.cell_value(a,c)))
                if d - c > 1:
                    # arr.append({(a, b): table.cell_value(a,b)})
                    for i in range(c, d):
                        dic.update({i: table.cell_value(a + 1, i).replace('\n', '').replace('(', '').replace(')',
                                                                                                             '').replace(
                            '（', '').replace('）', '')})
                else:
                    dic.update({c: table.cell_value(a, c).replace('\n', '').replace('(', '').replace(')', '').replace(
                        '（', '').replace('）', '')})
            return [dic[k] for k in sorted(dic)]
        else:
            dataFile = []
            for rowNum in range(table.nrows):
                if table.row_values(rowNum)[0] == '':
                    pass
                dataFile.append(table.row_values(rowNum))
            return dataFile[0]

# import pandas as pd


# excelFile = 'file/demo.xlsx'
# excelFile1 = 'file/demo1.xlsx'
# exs = excelFile1.endswith('.xls')
# print(exs)

# ex=Excelcheck(file=excelFile)
# ex1=Excelcheck(file=excelFile1)
# v1=ex.getheader()
# v2=ex1.getheader()
# dff=ex.getdffheader(v1,v2)
# arr1=[]
# for d in dff:
#     if len(d)>2:
#         arr1.append({'fieldname':"".join([l[0] for l in lazy_pinyin(d)]),'fieldalias':d})
#     else:
#         arr1.append({'fieldname':"".join([l for l in lazy_pinyin(d)]),'fieldalias':d})
# print(arr1)

# excel_content=pd.read_excel(excelFile, "Sheet1",header=0)
# # 获取表头，使用columns属性，此时取出的值为DataFrame，所以需要使用tolist()转换为数组
# excel_content.columns.get_level_values(0).values
# excel_header = excel_content.columns.tolist()
# print(excel_header)

# 根据表头取出对应列数据
# for i in excel_header:
#     print(excel_content[i].values.tolist())
# print(ex1.to_json(file=excelFile,isJson=1))